Generally, a database stores data with correlation, and the data stored in the database are needed to be updated, inserted, and deleted. Thus, the database is managed by a software system called a database management system (DBMS). The DBMS is an integrated data management system that allows application programs to search or change desired data so as to provide consistent results to the application programs at all times.
In order to fetch the data stored in the database, a query is written using a query language called a structured query language (SQL). An operation for converting an SQL query to a query execution plan that can be conducted in the database is executed by a query compiler. The query execution plan is comprised of a tree having nodes of operations required to execute a query.
In an actual query compiler, the procedure of converting an SQL query to a query execution plan is performed as follows.
First, a query is parsed to form a parse tree structure. Next, a query rewrite process is performed to transform the parse tree to a more general form of the parse tree. Next, the transformed parse tree is again transformed to an execution plan having a minimum cost based on statistic information.
Of course, a result may be correctly obtained even without any query rewriting process. Making the parse tree into a more general form by the query rewriting is to make more execution plans, which results in raising the possibility to obtain the more optimized execution plan.
Further, the query rewriting may also be used to remove portions that are included in the original query but that need not to be operated, thereby improving the performance of the query compiler as well as query execution.
Therefore, it is desired to rewrite the queries so that the optimized execution plans can be established in the query compiler.
In transforming a query having correlated scalar subqueries, one way is to transform a subquery into a join type by unnesting it. With regard to the above, U.S. Pat. No. 8,521,723, issued on Aug. 27, 2013 and entitled “TRANSFORMING CORRELATED SCLAR SUBQUERIES”, discloses a method for transforming an initial query having a scalar subquery with correlated predicates into a transformed query having transformed predicates that are not in the scalar subquery.
However, the above-mentioned prior art merely discloses a commonly known method to transform a subquery into a join type by unnesting it and fails to describe how to perform an unnest operation on queries having correlated scalar subqueries that are identified depending on the type of the identified correlated scalar subquery into the type of quasi-JOIN. Further, the above-mentioned prior art, when performing SQL Join queries, carries out a Group by Aggregation and an Aggregation Join in consecutive order without utilizing common elements between them. Accordingly, a continued computation without utilizing common elements entails an exponentially increased workload of a processing unit such as CPUs, which results in a large amount of outputs.